package com.seres.excelutils;

import com.seres.enums.ErrorEnum;
import com.seres.exception.ServerException;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

@Slf4j
public class ExcelExportXls<T> {


    private final static String SheetName = "sheet1";

    /**
     * @param headers: 题头
     * @param list:      数据beanList(必须参数)
     * @param fileName:  文件名(必须参数 例"D:\\123.xls")
     * @param response:
     * @description
     * @author 谭博文
     * @Date 2020/8/14 14:48
     * @return: void
     */
    public static  void  exportToExcel(String[] headers, List<?> list, String fileName, HttpServletResponse response) {
        if (list.size() <= 0) {
            log.error("数据源没有数据");
            return;
        }
        expExcel(headers, list, fileName, response);
    }

    /**
     * @param headers: 题头
     * @param list:      数据beanList
     * @param fileName:  文件名
     * @param response:
     * @description
     * @author 谭博文
     * @Date 2020/8/14 14:47
     * @return: void
     */
    @SuppressWarnings("resource")
    private static <T> void expExcel(String[] headers, List<T> list, String fileName, HttpServletResponse response) {
        // 声明一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格页
        HSSFSheet sheet = workbook.createSheet(SheetName);
        // 生成一个表格样式(表头样式)
        HSSFCellStyle style1 = workbook.createCellStyle();
        // 设置单元格背景样式颜色
//        style1.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 普通颜色填充
//        style1.setFillForegroundColor(HSSFColorPredefined.LIGHT_BLUE.getIndex());// 蓝色背景
        // 设置Excel中的边框(表头的边框)
        style1.setAlignment(HorizontalAlignment.CENTER);// 文字水平居中
        style1.setVerticalAlignment(VerticalAlignment.CENTER);// 文字垂直居中
        style1.setBorderBottom(BorderStyle.THIN);// 底部边框线样式(细实线)
        style1.setBottomBorderColor(HSSFColorPredefined.BLACK.getIndex());// 底部边框线颜色
        style1.setBorderLeft(BorderStyle.THIN);// 左边框线样式(细实线)
        style1.setLeftBorderColor(HSSFColorPredefined.BLACK.getIndex());// 左边框线颜色
        style1.setBorderRight(BorderStyle.THIN);// 右边框线样式(细实线)
        style1.setRightBorderColor(HSSFColorPredefined.BLACK.getIndex());// 右边框线颜色
        style1.setBorderTop(BorderStyle.THIN);// 顶部边框线样式(细实线)
        style1.setTopBorderColor(HSSFColorPredefined.BLACK.getIndex());// 顶部边框线颜色
        style1.setWrapText(true);// 自动换行
        // 设置字体
        HSSFFont font1 = workbook.createFont();// 生成一个字体样式
        font1.setFontHeightInPoints((short) 14); // 字体高度(大小)
        font1.setFontName("等线"); // 字体
        font1.setColor(HSSFColorPredefined.BLACK.getIndex());
//        font1.setBold(true);// 加粗
        style1.setFont(font1);// 把字体应用到当前样式

        // 生成一个表格样式(列表样式---非表头)
        HSSFCellStyle style2 = workbook.createCellStyle();
        // 设置单元格背景样式颜色
//        style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 普通颜色填充
//        style2.setFillForegroundColor(HSSFColorPredefined.LIGHT_GREEN.getIndex());// 蓝色背景
        // 设置Excel中的边框(表头的边框)
        style2.setAlignment(HorizontalAlignment.CENTER);// 文字水平居中
        style2.setVerticalAlignment(VerticalAlignment.CENTER);// 文字垂直居中
        style2.setBorderBottom(BorderStyle.THIN);// 底部边框线样式(细实线)
        style2.setBottomBorderColor(HSSFColorPredefined.BLACK.getIndex());// 底部边框线颜色
        style2.setBorderLeft(BorderStyle.THIN);// 左边框线样式(细实线)
        style2.setLeftBorderColor(HSSFColorPredefined.BLACK.getIndex());// 左边框线颜色
        style2.setBorderRight(BorderStyle.THIN);// 右边框线样式(细实线)
        style2.setRightBorderColor(HSSFColorPredefined.BLACK.getIndex());// 右边框线颜色
        style2.setBorderTop(BorderStyle.THIN);// 顶部边框线样式(细实线)
        style2.setTopBorderColor(HSSFColorPredefined.BLACK.getIndex());// 顶部边框线颜色
//        style2.setWrapText(true);// 自动换行
        // 设置字体
        HSSFFont font2 = workbook.createFont();// 生成一个字体样式
        font2.setFontHeightInPoints((short) 12); // 字体高度(大小)
        font2.setFontName("等线"); // 字体
        font2.setColor(HSSFColorPredefined.BLACK.getIndex());
//        font2.setBold(true);// 加粗
        style2.setFont(font2);// 把字体应用到当前样式
        OutputStream outputStream = null;
        try {
            fillSheet(sheet, style1, style2, list, headers);
            fileName = fileName + ".xls";
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            response.setHeader("Pragma", "No-cache");
            outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * @param sheet:    Excel表格
     * @param style1:   表头样式
     * @param style2:   列表样式
     * @param list:     数据源
     * @param headers: 表头
     * @description
     * @author 谭博文
     * @Date 2020/8/14 14:46
     * @return: void
     */
    private static <T> void fillSheet(HSSFSheet sheet, HSSFCellStyle style1, HSSFCellStyle style2, List<T> list,
                                      String[] headers) throws Exception {
        HSSFRow row = sheet.createRow(0);// 第一行(标题行)
        // 填充第一行列表题头
        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(style1);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        // 遍历集合数据，产生数据行
        Iterator<T> it = list.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            T t = (T) it.next();
            // 获取bean值
            List<String> beanV = sortGetBeanVale(t);
            if(headers.length!=beanV.size()){
                log.error("数据与表头不匹配");
                throw new ServerException(ErrorEnum.SERVER_ERROR);
            }
            for (int i = 0; i < beanV.size(); i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(style2);
                String objValue = beanV.get(i);
                if (objValue != null) {
                    cell.setCellValue(objValue);
                }
            }

        }
        setColumnAutoSize(sheet);
    }

    private static void setColumnAutoSize(HSSFSheet sheet) {
        for (int i = 0; i < sheet.getRow(0).getPhysicalNumberOfCells(); i++) {
            sheet.autoSizeColumn(i);// 自动设置列宽
        }
    }


    public static List<String> sortGetBeanVale(Object obj) {
        List<String> list = new ArrayList<>();
        Field fields[] = obj.getClass().getDeclaredFields();
        String[] beanName = new String[fields.length];
        Object[] beanValue = new Object[fields.length];
        Object[] beanSourceType = new Object[fields.length];
        try {
            Field.setAccessible(fields, true);
            for (int i = 0; i < beanName.length; i++) {
                beanName[i] = fields[i].getName();
                beanValue[i] = fields[i].get(obj);
                beanSourceType[i] = fields[i].getType().getName();
                if(beanValue[i]==null||"".equals(beanValue[i])||"null".equals(beanValue[i])){
                    list.add("");
                }else{
                    list.add(String.valueOf(beanValue[i]));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

}
